/*
 * Copyright (C) <2009>  <Matthew Dickinson>
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
*/

package com.pulsarwebdevelopment.concordance;

import java.io.File;
import java.sql.*;
import java.util.ArrayList;

import android.util.Log;

public class DatabaseConnection {

	Connection dbConnection = null;
	
	//ON DELETE CASCADE ON UPDATE RESTRICT

	static final String createConcordanceTable = "CREATE TABLE concordance_table "+
		"(id int identity," +	//h2 version
		//"(id int PRIMARY KEY generated always as identity, "+	//derby version
		"title varchar(45) NOT NULL," +
		"description varchar(300) NOT NULL)";
	
	static final String createBookRec = "CREATE TABLE book_rec "+
		"(id int identity," +	//h2 version
		//"(id int PRIMARY KEY generated always as identity, "+	//derby version
		"book_name varchar(45) NOT NULL," +
		"concordance int NOT NULL CONSTRAINT book_fk REFERENCES concordance_table)";
	
	static final String createOccurenceRec = "CREATE TABLE occurrence_rec "+
		"(id int identity," +	//h2 version
		//"(id int PRIMARY KEY generated always as identity, "+	//derby version
		"word int NOT NULL CONSTRAINT occ_word_fk REFERENCES word_rec,"+
		"verse int  NOT NULL CONSTRAINT occ_ver_fk REFERENCES verse_rec,"+
		"concordance int NOT NULL CONSTRAINT occ_conc_fk REFERENCES concordance_table,"+
		"position smallint NOT NULL)";
	
	static final String createVerseRec = "CREATE TABLE verse_rec " +
		"(id int identity," +	//h2 version
		//"(id int PRIMARY KEY generated always as identity, "+	//derby version
		"concordance int NOT NULL CONSTRAINT ver_conc_fk REFERENCES concordance_table,"+
		"book int NOT NULL CONSTRAINT ver_book_fk REFERENCES book_rec,"+
		"chapter smallint NOT NULL,"+
		"verse_num smallint NOT NULL)";
	
	static final String createWordRec = "CREATE TABLE word_rec " +
		"(id int identity," +	//h2 version
		//"(id int PRIMARY KEY generated always as identity, "+	//derby version
		"word varchar(30) NOT NULL)";
		
	static final String createWordRecIndex = "CREATE INDEX word_index ON word_rec (word)";
	

	public boolean createTables(){
		try{
			System.out.println("creating concordance_table");
			dbConnection.createStatement().execute(createConcordanceTable);
		}catch(Exception e){
			e.printStackTrace();
			System.err.println("concordance_table creation failed");
			Log.e("conc", "concordance_table create failed", e);
			return false;
		}
		try{
			System.out.println("creating book_rec");
			dbConnection.createStatement().execute(createBookRec);
		}catch(Exception e){
			e.printStackTrace();
			System.err.println("book_rec creation failed");
			Log.e("conc", "book_rec create failed", e);
			return false;
		}
		try{
			System.out.println("creating verse_rec");
			dbConnection.createStatement().execute(createVerseRec);
		}catch(Exception e){
			e.printStackTrace();
			System.err.println("verse_rec creation failed");
			Log.e("conc", "verse_rec create failed", e);
			return false;
		}
		try{
			System.out.println("creating word_rec");
			dbConnection.createStatement().execute(createWordRec);
		}catch(Exception e){
			e.printStackTrace();
			System.err.println("word_rec creation failed");
			Log.e("conc", "word_rec create failed", e);
			return false;
		}
		try{
			System.out.println("creating occurence_rec");
			dbConnection.createStatement().execute(createOccurenceRec);
		}catch(Exception e){
			e.printStackTrace();
			System.err.println("occurence_rec creation failed");
			Log.e("conc", "occurence_rec create failed", e);
			return false;
		}
		try{
			System.out.println("creating word_rec index");
			dbConnection.createStatement().execute(createWordRecIndex);
		}catch(Exception e){
			e.printStackTrace();
			System.err.println("word_rec index creationfailed");
			Log.e("conc", "word_rec create failed", e);
			return false;
		}
		return true;
	}

	public boolean removeTable(String table){
		//String drop = "drop table " +;
		try{
			dbConnection.createStatement().execute("drop table " + table);
			//dropTable.setString(1, table);
		}catch(Exception e){
			e.printStackTrace();
			return false;
		}
		return true;
	}
	
	public ArrayList<String> getTableNames(){
		ArrayList<String> tables = new ArrayList<String>();
		try{
			String[] names = { "TABLE"}; 
			ResultSet tableNames = dbConnection.getMetaData().getTables(null, null, null, names);
			while (tableNames.next()){
				tables.add(tableNames.getString( "TABLE_NAME"));
			}
		}catch (Exception e){
			e.printStackTrace();
		}
		return tables;
	}


	public boolean connect(File baseDir){
		try{
			
			//TODO make this cross-platform
			//File baseDir = android.content.ContextWrapper.
			File systemDir = new File(baseDir,"databases");

			boolean alreadyExists = systemDir.exists();
			//System.out.println("Database created in " + systemDir);

			if(alreadyExists){
				System.out.println("Using existing database: " + systemDir);
			}else{
				System.out.println("Creating database: " + systemDir);
				systemDir.mkdir();
			}
			Class.forName("org.h2.Driver");
			String strUrl = "jdbc:h2:" + systemDir.toString() + "/db";
			dbConnection = DriverManager.getConnection(strUrl);
			
			if(!alreadyExists){
				//create tables
				createTables();
			}
			
		}catch(Exception e){
			e.printStackTrace();
			return false;
		}
		return true;
	}
}
